package sw6.visualschedule.persistence;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;
import java.util.Random;

import sw6.visualschedule.entities.ActivityStatus;
import sw6.visualschedule.entities.DbDate;
import sw6.visualschedule.entities.RepeatPattern;
import sw6.visualschedule.entities.ScheduleActivity;
import sw6.visualschedule.entities.Step;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;

import com.j256.ormlite.android.apptools.OrmLiteSqliteOpenHelper;
import com.j256.ormlite.support.ConnectionSource;
import com.j256.ormlite.table.TableUtils;

public class DatabaseHelper extends OrmLiteSqliteOpenHelper {
	
	private static final String AUDIO_EXTENSION = ".3gp";
	
    private static final String DATABASE_NAME = "visualschedule.db";
    private static final int DATABASE_VERSION = 1209;
    private static final String IMG_EXTENSION = ".jpg";
    
    private final static String LOG_NAME = "DatabaseHelper";

    private transient final Context mContext;
    
    /**
     * Initializes the database helper
     * @param ctx the context to run in.
     */
    public DatabaseHelper(final Context ctx) {
        super(ctx, DATABASE_NAME, null, DATABASE_VERSION);
        mContext = ctx;       
    }

    @Override
    public void onCreate(final SQLiteDatabase sqliteDatabase, final ConnectionSource connection) {
        try {
            // TODO: Add more classes here
            TableUtils.createTable(connection, ScheduleActivity.class);
            TableUtils.createTable(connection, Step.class);
            TableUtils.createTable(connection, DbDate.class);
            TableUtils.createTable(connection, RepeatPattern.class);
            TableUtils.createTable(connection, ActivityStatus.class);
            
            fillDatabaseWithDummyData();
        } catch (SQLException e) {
        	throw new RuntimeException(LOG_NAME, e);
        }
    }

    @Override
    public void onUpgrade(final SQLiteDatabase sqliteDatabase, final ConnectionSource connection, final int oldVersion, final int newVersion) {
        try {
            // TODO: Add more tables here
            TableUtils.dropTable(connection, ScheduleActivity.class, true);
            TableUtils.dropTable(connection, Step.class, true);
            TableUtils.dropTable(connection, DbDate.class, true);
            TableUtils.dropTable(connection, RepeatPattern.class, true);
            TableUtils.dropTable(connection, ActivityStatus.class, true);
            
            onCreate(sqliteDatabase, connection);
        } catch (SQLException e) {
        	throw new RuntimeException(LOG_NAME, e);
        }
    }
    
    /**
     * Drops all tables and recreates them.
     * Used by unit tests only!!!
     * @throws SQLException
     */
    public void recreateDatabase() throws SQLException {
        final SQLiteDatabase database = getWritableDatabase();
        final ConnectionSource connection = getConnectionSource();
        
        TableUtils.dropTable(connection, ScheduleActivity.class, true);
        TableUtils.dropTable(connection, Step.class, true);
        TableUtils.dropTable(connection, DbDate.class, true);
        TableUtils.dropTable(connection, RepeatPattern.class, true);
        TableUtils.dropTable(connection, ActivityStatus.class, true);
        
        onCreate(database, connection);
    }
    
    @SuppressWarnings("unused")
	private void fillDatabaseWithDummyData() {
        final ScheduleActivityRepo activityService = new ScheduleActivityRepo(mContext);
        final Random rand = new Random();        
        final Date date = new Date();
        final GregorianCalendar cal = new GregorianCalendar();
        
        final StepRepo mStepService = new StepRepo(mContext);
        final DateRepo mDateService = new DateRepo(mContext);
        final List<Integer> activities = new ArrayList<Integer>();
        Step step;
        ScheduleActivity activity;
        DbDate dbdate;
        
        //Wake up activity
        activity = new ScheduleActivity("Good morning!");       
        activity.setImage("/mnt/sdcard/Visual Schedule Media/Visual Schedule Images/alarmclock"+IMG_EXTENSION);
        activityService.create(activity);
        date.setHours( 7 );
    	date.setMinutes( 30  );    	
    	dbdate = new DbDate(activity, date);
    	mDateService.create(dbdate);    	
    	activityService.update(activity);    	        
    	activity = activityService.getById(activity.getId());
    	
        step = new Step(activity, "Turn alarm clock off");
        step.setImage("/mnt/sdcard/Visual Schedule Media/Visual Schedule Images/alarmclock"+IMG_EXTENSION);
        mStepService.create(step);
        
        step = new Step(activity, "Put on clothes");
        step.setImage("/mnt/sdcard/Visual Schedule Media/Visual Schedule Images/put_on_shirt"+IMG_EXTENSION);
        mStepService.create(step);
        
        step = new Step(activity, "Brush teeth");
        step.setImage("/mnt/sdcard/Visual Schedule Media/Visual Schedule Images/toothbrush"+IMG_EXTENSION);
        mStepService.create(step);
        
        step = new Step(activity, "Eat breakfast");
        step.setImage("/mnt/sdcard/Visual Schedule Media/Visual Schedule Images/breakfast"+IMG_EXTENSION);
        mStepService.create(step);
      
        //Go to school activity
        activity = new ScheduleActivity("Go to school");       
        activity.setImage("/mnt/sdcard/Visual Schedule Media/Visual Schedule Images/schoolbus"+IMG_EXTENSION);
        activityService.create(activity);
        date.setHours(8);
    	date.setMinutes(35);    	
    	dbdate = new DbDate(activity, date);
    	mDateService.create(dbdate);    	
    	activityService.update(activity);    	        
    	activity = activityService.getById(activity.getId());
    	
        step = new Step(activity, "Get lunch");
        step.setImage("/mnt/sdcard/Visual Schedule Media/Visual Schedule Images/madpakke"+IMG_EXTENSION);
        mStepService.create(step);
        
        step = new Step(activity, "Lunch in backpack");
        step.setImage("/mnt/sdcard/Visual Schedule Media/Visual Schedule Images/madpakkeitaske"+IMG_EXTENSION);
        mStepService.create(step);
        
        step = new Step(activity, "Put on backpack");
        step.setImage("/mnt/sdcard/Visual Schedule Media/Visual Schedule Images/husktaske"+IMG_EXTENSION);
        mStepService.create(step);
        
        step = new Step(activity, "Get on bus");
        step.setImage("/mnt/sdcard/Visual Schedule Media/Visual Schedule Images/schoolbus"+IMG_EXTENSION);
        mStepService.create(step);
        
        //Study activity
        activity = new ScheduleActivity("Study math");       
        activity.setImage("/mnt/sdcard/Visual Schedule Media/Visual Schedule Images/math"+IMG_EXTENSION);
        activityService.create(activity);
        date.setHours(9);
    	date.setMinutes(20);
    	dbdate = new DbDate(activity, date);
    	mDateService.create(dbdate);
    	activityService.update(activity);    	        
    	activity = activityService.getById(activity.getId());
    	
        step = new Step(activity, "Find book");
        step.setImage("/mnt/sdcard/Visual Schedule Media/Visual Schedule Images/mathbook"+IMG_EXTENSION);
        mStepService.create(step);
        
        step = new Step(activity, "Do exercises");
        step.setImage("/mnt/sdcard/Visual Schedule Media/Visual Schedule Images/studymath"+IMG_EXTENSION);
        mStepService.create(step);
        
        //Lunch activity
        activity = new ScheduleActivity("Eat lunch");       
        activity.setImage("/mnt/sdcard/Visual Schedule Media/Visual Schedule Images/lunch"+IMG_EXTENSION);
        activityService.create(activity);
        date.setHours(12);
    	dbdate = new DbDate(activity, date);
    	mDateService.create(dbdate);    	
    	activityService.update(activity);    	        
    	activity = activityService.getById(activity.getId());
    	
        step = new Step(activity, "Eat lunch");
        step.setImage("/mnt/sdcard/Visual Schedule Media/Visual Schedule Images/eat"+IMG_EXTENSION);
        mStepService.create(step);
        
        //soccer activity
        activity = new ScheduleActivity("Play soccer");       
        activity.setImage("/mnt/sdcard/Visual Schedule Media/Visual Schedule Images/soccer"+IMG_EXTENSION);
        activityService.create(activity);
        date.setHours(13);
    	date.setMinutes(15);    	
    	dbdate = new DbDate(activity, date);
    	mDateService.create(dbdate);    	
    	activityService.update(activity);    	        
    	activity = activityService.getById(activity.getId());
    	
        step = new Step(activity, "Put on soccer uniform");
        step.setImage("/mnt/sdcard/Visual Schedule Media/Visual Schedule Images/soccer_uniform"+IMG_EXTENSION);
        mStepService.create(step);
        
        step = new Step(activity, "Put on soccer shoes");
        step.setImage("/mnt/sdcard/Visual Schedule Media/Visual Schedule Images/soccer_shoes"+IMG_EXTENSION);
        mStepService.create(step);
        
        step = new Step(activity, "Go to the field and play");
        step.setImage("/mnt/sdcard/Visual Schedule Media/Visual Schedule Images/soccer_field"+IMG_EXTENSION);
        mStepService.create(step);
        
        step = new Step(activity, "Take a shower");
        step.setImage("/mnt/sdcard/Visual Schedule Media/Visual Schedule Images/shower"+IMG_EXTENSION);
        mStepService.create(step);
        
        step = new Step(activity, "Put on clothes");
        step.setImage("/mnt/sdcard/Visual Schedule Media/Visual Schedule Images/put_on_shirt"+IMG_EXTENSION);
        mStepService.create(step);
        
        
        
        //bus activity
        activity = new ScheduleActivity("Go home");       
        activity.setImage("/mnt/sdcard/Visual Schedule Media/Visual Schedule Images/bus"+IMG_EXTENSION);
        activityService.create(activity);
        date.setHours(15);
    	dbdate = new DbDate(activity, date);
    	mDateService.create(dbdate);    	
    	activityService.update(activity);    	        
    	activity = activityService.getById(activity.getId());
    	
        step = new Step(activity, "Take bus");
        step.setImage("/mnt/sdcard/Visual Schedule Media/Visual Schedule Images/bus"+IMG_EXTENSION);
        mStepService.create(step);
        
        
        /*
        //Add some random activities
        final int amountActivities = 4;
        final int amountStepsPerActivity = 5;
        
        for (int i = 1; i <= amountActivities; i++) {
        	final ScheduleActivity act = new ScheduleActivity("Activity Example " + i);        	
        	act.setImage("/mnt/sdcard/Visual Schedule Media/Visual Schedule Images/" + (i % 14) + IMG_EXTENSION);        
        	
        	activityService.create(act);
        	activities.add(act.getId());
        	
        	date.setHours( (cal.get(Calendar.HOUR_OF_DAY) + (rand.nextInt(10)) ) % 22  );
        	date.setMinutes( (cal.get(Calendar.MINUTE) + (rand.nextInt(60)) ) % 58  );
        	
        	dbdate = new DbDate(act, date);
        	mDateService.create(dbdate);
        	
        	activityService.update(act);
        }
        
        ScheduleActivity act = activityService.getById(activities.get(0));
        
        step = new Step(act, "Step 1");
		step.setImage("/mnt/sdcard/Visual Schedule Media/Visual Schedule Images/1" + IMG_EXTENSION);
		mStepService.create(step);        
		
		step = new Step(act, "Step 2");
		step.setImage("/mnt/sdcard/Visual Schedule Media/Visual Schedule Images/2" + IMG_EXTENSION);
		step.setAudio("/mnt/sdcard/Visual Schedule Media/Visual Schedule Audio/2" + AUDIO_EXTENSION);
		mStepService.create(step);        
		
        //Generate image only steps
        for (int i = 1; i <= amountActivities; i++) {
        	act = activityService.getById(activities.get(i-1));
        	for (int j = 1; j <= amountStepsPerActivity; j++) {
        		step = new Step(act, "Step " + j);
        		if (j % 2 == 0) {
        			step.setImage("/mnt/sdcard/Visual Schedule Media/Visual Schedule Images/" + j + IMG_EXTENSION);
        		} else {
        			step.setImage("/mnt/sdcard/Visual Schedule Media/Visual Schedule Images/" + ( j % 15) + IMG_EXTENSION);
        			step.setAudio("/mnt/sdcard/Visual Schedule Media/Visual Schedule Audio/" + (j % 6) + IMG_EXTENSION);
        		}
        		mStepService.create(step);
        	}
		}
		*/
    }
}